// ================================================================================
// 		File: PartyGrassrootsSuggestion.cs
// 		Desc: 
//  
// 		Called by:   
//               
// 		Auth: chenwenzhi（）
// 		Date: 2016-06-11 23:56
// ================================================================================
// 		Change History
// ================================================================================
// 		Date:		Author:				Description:
// 		--------	--------			-------------------
//    
// ================================================================================
// 
// ================================================================================
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using ZHDJ.Core;

namespace ZHDJ.Data.Models.Grassroots
{
    /// <summary>
    /// 群众心声
    /// </summary>   
    public class PartyGrassrootsSuggestion : EntityBase
    {
        #region 属性


        /// <summary>
        /// 所属小组
        /// </summary>
        public string ItemID { get; set; }

        /// <summary>
        /// 编号
        /// </summary>
        public string SerialNumber { get; set; }

        /// <summary>
        /// 用户名
        /// </summary>
        public string UserName { get; set; }

        /// <summary>
        /// 主题
        /// </summary>
        public string Title { get; set; }

        /// <summary>
        /// 联系形式
        /// </summary>
        public string MobileNo { get; set; }

        /// <summary>
        /// 工作单位
        /// </summary>
        public string WorkUnit { get; set; }

        /// <summary>
        /// 职务
        /// </summary>
        public string Post { get; set; }

        /// <summary>
        /// 内容
        /// </summary>
        public string Content { get; set; }

        /// <summary>
        /// /*状态(1-草稿，4-发布)*/ 状态(1-未受理/放弃，2-进行中，3-处理完成)
        /// </summary>
        public int Status { get; set; }

        /// <summary>
        /// 回复
        /// </summary>
        public string Reply { get; set; }

        /// <summary>
        /// 姓别
        /// </summary>
        public string Gender { get; set; }

        /// <summary>
        /// 年龄
        /// </summary>
        public int Age { get; set; }

        /// <summary>
        /// 联系地址
        /// </summary>
        public string Address { get; set; }

        /// <summary>
        /// 邮政编码
        /// </summary>
        public string Zip { get; set; }

        /// <summary>
        /// 政治面貌
        /// </summary>
        public string PoliticsLineament { get; set; }

        /// <summary>
        /// 处理方式
        /// </summary>
        public string HandlerBy { get; set; }

        /// <summary>
        /// 接待人员
        /// </summary>
        public string Receiver { get; set; }

        /// <summary>
        /// 收集时间
        /// </summary>
        public DateTime? CollectionTime { get; set; }



        /// <summary>
        /// 群众评价
        /// </summary>
        public string MassComment { get; set; }

        /// <summary>
        /// 创建人
        /// </summary>
        public string CreateBy { get; set; }

        /// <summary>
        /// 创建时间
        /// </summary>
        public DateTime? CreateTime { get; set; }

        /// <summary>
        /// 修改人
        /// </summary>
        public string ModifyBy { get; set; }

        /// <summary>
        /// 修改时间
        /// </summary>
        public DateTime? ModifyTime { get; set; }

        /// <summary>
        /// 是否公开
        /// </summary>
        public int publicity { get; set; }

        /// <summary>
        /// 组织
        /// </summary>
        public string BranchID { get; set; }

        /// <summary>
        /// 一级组织（山海天）
        /// </summary>
        public string OneLayer { get; set; }

        /// <summary>
        /// 二级组织（党委）
        /// </summary>
        public string TwoLayer { get; set; }

        /// <summary>
        /// 三级组织（支部）
        /// </summary>
        public string ThreeLayer { get; set; }

        /// <summary>
        /// 认领人
        /// </summary>
        public string Unclaimed { get; set; }

        #endregion

        #region 查询管理

        public static DataSet GetGridData(int pageIndex, int pageSize, string title, string status, string submitUser, string replyUser, string branchId, string groupId, string itemId, string publicity)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "Begin",Value = (pageIndex)*pageSize},
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "PageSize",Value = pageSize}
            };

            StringBuilder sb = new StringBuilder();
            sb.Append(@"select SQL_CALC_FOUND_ROWS data.ID,member.UserName as MUserName,ItemName,data.UserName,data.Title,
case data.Status when 1 then '待回复' else '已回复' end as StatusName,data.CreateTime,data.CollectionTime,data.ModifyTime,data.MobileNo,data.BranchID
FROM tblpartygrassrootssuggestion data 
left join tblpartygrassrootsgroupitem GI on data.ItemID=GI.ID 
left join tblpartygrassrootsgroup G on G.ID=GI.GroupID 
left join tblpartyorgmember member on data.ModifyBy=member.LoginID
                        where 1=1 ");

            if (!string.IsNullOrEmpty(title))
            {
                sb.Append(" and data.Title like ?Title ");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "Title", Value = string.Format("%{0}%", title) });
            }
            if (!string.IsNullOrEmpty(status))
            {
                //程序临时修改
                if (status == "1")
                {
                    sb.Append(" and data.Status=?Status ");
                    parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "Status", Value = status });
                }
                else
                {
                    sb.Append(" and data.Status <> '1' ");
                }
            }
            if (!string.IsNullOrEmpty(submitUser))
            {
                sb.Append(" and data.UserName like ?SubmitUser ");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "SubmitUser", Value = string.Format("%{0}%", submitUser) });
            }
            if (!string.IsNullOrEmpty(replyUser))
            {
                sb.Append(" and (data.ModifyBy like ?ReplyUser or member.UserName like ?ReplyUser)");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "ReplyUser", Value = string.Format("%{0}%", replyUser) });
            }
            if (!string.IsNullOrEmpty(branchId))
            {
                sb.Append(" and GI.BranchID in (select childbranchid from tblpartyorgbranchrelation where BranchID=?BranchID)");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "BranchID", Value = branchId });
            }
            if (!string.IsNullOrEmpty(groupId))
            {
                sb.Append(" and G.ID=?GroupID");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "GroupID", Value = groupId });
            }
            if (!string.IsNullOrEmpty(itemId))
            {
                sb.Append(" and data.ItemID=?ItemID");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "ItemID", Value = itemId });
            }
            if (publicity == "1")
            {
                sb.Append(" and data.publicity=1");
            }

            sb.Append(" order by data.CreateTime desc LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS();");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());
        }

        public static DataTable GetExportData(string title, string status, string submitUser, string replyUser, string branchId, string groupId, string itemId)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>();
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select data.Title,data.UserName,member.UserName as MUserName,ItemName,
case data.Status when 1 then '待回复' else '已回复' end as StatusName,data.CreateTime
FROM tblpartygrassrootssuggestion data 
left join tblpartygrassrootsgroupitem GI on data.ItemID=GI.ID 
left join tblpartygrassrootsgroup G on G.ID=GI.GroupID 
left join tblpartyorgmember member on data.ModifyBy=member.LoginID
                        where 1=1 ");

            if (!string.IsNullOrEmpty(title))
            {
                sb.Append(" and data.Title like ?Title ");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "Title", Value = string.Format("%{0}%", title) });
            }
            if (!string.IsNullOrEmpty(status))
            {
                sb.Append(" and data.Status=?Status ");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "Status", Value = status });
            }
            if (!string.IsNullOrEmpty(submitUser))
            {
                sb.Append(" and data.UserName like ?SubmitUser ");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "SubmitUser", Value = string.Format("%{0}%", submitUser) });
            }
            if (!string.IsNullOrEmpty(replyUser))
            {
                sb.Append(" and (data.ModifyBy like ?ReplyUser or member.UserName like ?ReplyUser)");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "ReplyUser", Value = string.Format("%{0}%", replyUser) });
            }
            if (!string.IsNullOrEmpty(branchId))
            {
                sb.Append(" and GI.BranchID in (select childbranchid from tblpartyorgbranchrelation where BranchID=?BranchID)");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "BranchID", Value = branchId });
            }
            if (!string.IsNullOrEmpty(groupId))
            {
                sb.Append(" and G.ID=?GroupID");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "GroupID", Value = groupId });
            }
            if (!string.IsNullOrEmpty(itemId))
            {
                sb.Append(" and data.ItemID=?ItemID");
                parameters.Add(new MySqlParameter() { DbType = DbType.String, ParameterName = "ItemID", Value = itemId });
            }

            sb.Append(" order by data.CreateTime desc");
            return MySqlHelper.ExecuteDataTable(sb.ToString(), parameters.ToArray());
        }


        public static DataSet GetOne(string id)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter(){DbType = DbType.String,ParameterName = "ID",Value =id},
                new MySqlParameter(){DbType = DbType.String,ParameterName = "DutyCategoryType",Value ="ZHDJ.Grassroots.MessageWay"},
                new MySqlParameter(){DbType = DbType.String,ParameterName = "DutyCategoryType2",Value ="ZHDJ.Grassroots.MassComment"}
            };

            StringBuilder sb = new StringBuilder();
            sb.Append(@"select SQL_CALC_FOUND_ROWS data.*,member.UserName as MUserName,GI.ItemName,
case data.Status when 1 then '待回复' else '已回复' end as StatusName,d.DDicMemberName as HandlerByName,d2.DDicMemberName as MassCommentName
FROM tblpartygrassrootssuggestion data 
left join tblpartygrassrootsgroupitem GI on data.ItemID=GI.ID 
left join tblpartygrassrootsgroup G on G.ID=GI.GroupID 
left join tblpartyorgmember member on data.ModifyBy=member.LoginID
left join tbldatadictionary d on (d.DDicType=?DutyCategoryType and data.HandlerBy=d.DDicMember)
left join tbldatadictionary d2 on (d2.DDicType=?DutyCategoryType2 and data.MassComment=d2.DDicMember)
                        where data.ID=?ID ");

            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());
        }
        #endregion
    }
}
